﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using _4T.Data;

namespace _4T.DAL
{
    public class StationDAL
    {
        public StationDAL() { }

        public int InsertStation(string stationID, string fullName, string addr, string desc)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "StationInsert";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@PersonalCode", SqlDbType.VarChar, 50).Value = stationID;
                    cmd.Parameters.Add("@FullName", SqlDbType.NText).Value = fullName;
                    cmd.Parameters.Add("@Address", SqlDbType.NText).Value = addr;
                    cmd.Parameters.Add("@Description", SqlDbType.NText).Value = desc;
                    cmd.Connection = conn;

                    result = Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return result;
        }

        public int UpdateStation(string stationID, string fullName, string addr, string desc)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "StationUpdate";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@PersonalCode", SqlDbType.VarChar, 50).Value = stationID;
                    cmd.Parameters.Add("@FullName", SqlDbType.NText).Value = fullName;
                    cmd.Parameters.Add("@Address", SqlDbType.NText).Value = addr;
                    cmd.Parameters.Add("@Description", SqlDbType.NText).Value = desc;
                    cmd.Connection = conn;

                    result = Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return result;
        }

        public CMS_Station GetStationByStationID(string stationID)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();

                CMS_Station station = null;
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "StationGet";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@PersonalCode", SqlDbType.VarChar, 50).Value = stationID;
                    cmd.Connection = conn;

                    reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        reader.Read();
                        station = GetStationFromReader(reader);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }

                return station;
            }

            return null;
        }

        public List<CMS_Station> GetStationList(int page, int pageSize, string searchText)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "StationGetList";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("page", SqlDbType.Int).Value = page;
                    cmd.Parameters.Add("pageSize", SqlDbType.Int).Value = pageSize;
                    cmd.Parameters.Add("searchText", SqlDbType.VarChar, 50).Value = searchText;
                    cmd.Connection = conn;
                    reader = cmd.ExecuteReader();
                    if (reader != null)
                    {
                        List<CMS_Station> list = new List<CMS_Station>();
                        while (reader.Read())
                        {
                            CMS_Station item = GetStationFromReader(reader);
                            list.Add(item);
                        }
                        return list;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return null;
        }

        private CMS_Station GetStationFromReader(SqlDataReader reader)
        {
            string stationID = reader.GetString(0);

            string fullName = string.Empty;
            if (reader.GetValue(1) != DBNull.Value)
                fullName = reader.GetString(1);

            string address = string.Empty;
            if (reader.GetValue(2) != DBNull.Value)
                address = reader.GetString(2);

            string description = string.Empty;
            if (reader.GetValue(3) != DBNull.Value)
                description = reader.GetString(3);

            return new CMS_Station(stationID, fullName, address, description);
        }
    }
}